package itheima03;

import domain.Users;
import utils.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xxx
 * @date 2021/1/4 17:53
 * @description
 */
public class Test {

@org.junit.Test
public void addone() throws SQLException {
    Connection connection= JDBCUtil.getConnection();

    String sql="insert into  users(sid,uname,upass) values(?,?,?)";
    PreparedStatement preparedStatement=connection.prepareStatement(sql);
    preparedStatement.setObject(1,2);
    preparedStatement.setObject(2,"wkr");
    preparedStatement.setObject(3,"1994");

    int i=preparedStatement.executeUpdate();
    if(i>0){
        System.out.println("更新成功");
    }else {
        System.out.println("更新失败");
    }
    //接口PreparedStatement是接口statement的子类
    JDBCUtil.release(connection,preparedStatement,null);
  }
    @org.junit.Test
    public void updateone() throws SQLException {
    Connection connection=JDBCUtil.getConnection();
    String sql="update users set uname=?,upass=? where sid=?";
    PreparedStatement preparedStatement=connection.prepareStatement(sql);
    preparedStatement.setObject(1,"hahah");
    preparedStatement.setObject(2,"wo111");
    preparedStatement.setObject(3,"2");
    int i=preparedStatement.executeUpdate();
    if(i>0){
        System.out.println("更新成功");
    }else {
        System.out.println("更新失败");
    }
    JDBCUtil.release(connection,preparedStatement,null);
    }

    @org.junit.Test
    public  void queryUserByUid() throws SQLException {
    Connection connection=JDBCUtil.getConnection();
    String sql="select * from users where sid=?";
    PreparedStatement preparedStatement=connection.prepareStatement(sql);
    preparedStatement.setObject(1,1);

    ResultSet resultSet=preparedStatement.executeQuery();
    if (resultSet.next()){
        String sid=resultSet.getString("sid");
        String sname=resultSet.getString("uname");
        String spass=resultSet.getString("upass");
        Users users=new Users();
        users.setUid(sid);
        users.setUname(sname);
        users.setUpass(spass);

        System.out.println(users);

    }else {
        System.out.println("没有查询到数据");
    }

     JDBCUtil.release(connection,preparedStatement,null);
    }
    @org.junit.Test
    public  void queryAll() throws SQLException {
    List<Users> list=new ArrayList<>();
    Connection connection=JDBCUtil.getConnection();
    String sql="select * from users";
    PreparedStatement preparedStatement=connection.prepareStatement(sql);
    ResultSet resultSet=preparedStatement.executeQuery();
    while (resultSet.next()){
        Users users=new Users();
        String sid=resultSet.getString("sid");
        String  uname=resultSet.getString("uname");
        String  upass=resultSet.getString("upass");
        users.setUid(sid);
        users.setUname(uname);
        users.setUpass(upass);
        list.add(users);;
       }
        if(list.size()!=0){
            for(Users users1:list){
                System.out.println(users1);
            }
        }else {
            System.out.println("查不到此数据");
        }

        JDBCUtil.release(connection,preparedStatement,resultSet);
    }
    @org.junit.Test
    public  void deletequll() throws SQLException {
    Connection connection=JDBCUtil.getConnection();
    String sql="delete from users where sid=?";
    PreparedStatement preparedStatement=connection.prepareStatement(sql);

        for (int i = 0; i <5 ; i++) {
            preparedStatement.setObject(1,i);
            int aaa=preparedStatement.executeUpdate();
            if(aaa>0){
                System.out.println("删除第"+i+"条数据成功");
            }else {
                System.out.println("删除第"+i+"条数据失败");
            }
        }

        JDBCUtil.release(connection,preparedStatement,null);
    }


}
